package top.lingkang.springboothibernate.controller;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import top.lingkang.springboothibernate.entity.UserBean;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.List;
import top.lingkang.springboothibernate.entity.UserRoleBean;

@RestController
public class WebController {
    @Autowired
    private DataSource dataSource;

    @GetMapping("")
    public Object index() throws Exception {
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_user");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
        return null;
    }

    // import org.hibernate.SessionFactory;
    @Autowired
    private SessionFactory sessionFactory;

    /**
     * 此处需要注意，hibernate的增删改需要事务提交，否则不能保存到数据库
     * import org.springframework.transaction.annotation.Transactional;
     */
    @Transactional
    @GetMapping("add")
    public Object add() {
        UserBean bean = new UserBean();
        bean.setCreateTime(new Date());
        bean.setUsername("lingkang");
        bean.setNickname("凌康");
        bean.setPassword("123456");
        System.out.println(sessionFactory.getCurrentSession().save(bean));
        return bean;
    }

    /**
     * 此处需要注意，hibernate的增删改需要事务提交，否则不能保存到数据库
     * import org.springframework.transaction.annotation.Transactional;
     */
    @Transactional
    @GetMapping("update")
    public Object update() {
        Session currentSession = sessionFactory.getCurrentSession();
        List<UserBean> id = currentSession.createQuery("from UserBean where id=:id")
                .setParameter("id", "1414241573973135360").list();
        System.out.println(id);
        if (!id.isEmpty()) {
            UserBean bean = id.get(0);
            bean.setNickname("凌康，你好！");
            // 更新
            currentSession.update(bean);
        }
        return id;
    }

    /**
     * 此处需要注意，hibernate的增删改需要事务提交，否则不能保存到数据库
     * import org.springframework.transaction.annotation.Transactional;
     */
    @Transactional
    @GetMapping("delete")
    public Object delete() {
//        UserBean bean = new UserBean();
//        bean.setId("1414240012135960576");
        // 缺点，不存在的数据会报异常
        // sessionFactory.getCurrentSession().delete(bean);

        // 推荐  删除不会报异常
        int res = sessionFactory.getCurrentSession().createQuery("delete from UserBean  where id=:id")
                .setParameter("id", "1414243500257579008").executeUpdate();
        System.out.println(res);
        return res;
    }

    /**
     * 此处需要注意，hibernate的增删改需要事务提交，否则不能保存到数据库
     * import org.springframework.transaction.annotation.Transactional;
     */
    @Transactional
    @GetMapping("OneToOne")
    public Object OneToOne(){
        Session currentSession = sessionFactory.getCurrentSession();

        UserBean bean = new UserBean();
        bean.setCreateTime(new Date());
        bean.setUsername("lingkang");
        bean.setNickname("凌康");
        bean.setPassword("123456");
        currentSession.save(bean);

        UserRoleBean roleBean=new UserRoleBean();
        roleBean.setRoleId("666");
        // set user id
        roleBean.setUserId(bean.getId());
        currentSession.save(roleBean);

        // select 一对一
        List<UserBean> from_userBean = currentSession.createQuery("from UserBean").list();
        return from_userBean;
    }

    @GetMapping("get")
    public Object get(){
        List from_userBean = sessionFactory.getCurrentSession().createQuery(
            "from UserBean"
        ).list();
        return from_userBean;
    }

}
